package br.unipampa.dados;

import br.unipampa.view.ApoioDeInterface;
import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author Gabriel Moro
 * @since 15/03/2013
 * @version 1.2
 */
public class DBTelefone extends DML {

    private ArrayList<Object> values;

    public DBTelefone(Connection con) {
        super.connection = (com.mysql.jdbc.Connection) con;
        super.table = "telefones";
    }

    @Override
    public ArrayList<Object> select(String coluna) {
        try {
            super.statement = (Statement) super.connection.createStatement();
            super.resultSet = super.statement.executeQuery("SELECT nome_telefones,telefone_telefones FROM " + super.table + " WHERE nome_telefones LIKE '%" + coluna + "%' ORDER BY nome_telefones ASC;");
            this.values = new ArrayList<>();
            while (super.resultSet.next()) {
                this.values.add(super.resultSet.getString(1));
                this.values.add(super.resultSet.getString(2));
            }
            if (this.values.isEmpty()) {
                ApoioDeInterface.mensagemNaoExisteContato();
            }
            return this.values;
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
        return null;
    }

    public ArrayList<Object> selected() {
        try {
            super.statement = (Statement) super.connection.createStatement();
            super.resultSet = super.statement.executeQuery("SELECT nome_telefones,telefone_telefones FROM " + super.table + " ORDER BY nome_telefones ASC;");
            this.values = new ArrayList<>();
            while (super.resultSet.next()) {
                this.values.add(super.resultSet.getString(1));
                this.values.add(super.resultSet.getString(2));
            }
            if (this.values.isEmpty()) {
                ApoioDeInterface.mensagemNaoExisteContato();
            }
            return this.values;
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
        return null;
    }

    @Override
    public int insert(String valores) {
        try {
            super.statement = (Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("INSERT " + super.table + "(nome_telefones,telefone_telefones) VALUES(" + valores + ");");
            return registrosAfetados;
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
        return 0;
    }

    public int inserted(String valores, String nome) {
        try {
            if (!verificaExistenciaDeRegistro(nome)) {
                super.statement = (Statement) super.connection.createStatement();
                int registrosAfetados = super.statement.executeUpdate("INSERT " + super.table + "(nome_telefones,telefone_telefones) VALUES(" + valores + ");");
                return registrosAfetados;
            } else {
                ApoioDeInterface.mensagemContatoExistente();
            }
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
        return 0;
    }

    @Override
    public int update(String atributoX, String valores) {
        try {
            super.statement = (Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("UPDATE " + super.table + " SET telefone_telefones= '" + valores + "' WHERE nome_telefones= '" + atributoX + "';");
            return registrosAfetados;
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
        return 0;
    }

    @Override
    public int delete(String valor) {
        try {
            super.statement = (Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("DELETE FROM " + super.table + " WHERE nome_telefones= '" + valor + "';");
            return registrosAfetados;
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
        return 0;
    }

    public boolean verificaExistenciaDeRegistro(String nome) throws SQLException {
        try {
            super.statement = (Statement) super.connection.createStatement();
            super.resultSet = super.statement.executeQuery("SELECT nome_telefones FROM " + super.table + " WHERE nome_telefones= '" + nome + "';");
            ArrayList<String> valores = new ArrayList<>();
            while (super.resultSet.next()) {
                valores.add(super.resultSet.getString(1));
            }
            if (valores.isEmpty()) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        } finally {
            super.statement.close();
            super.resultSet.close();
        }
        return true;
    }

    @Override
    public void closedConnection() {
        try {
            super.statement.close();
            super.resultSet.close();
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
    }
}
